package tools;

import java.io.FileNotFoundException;
import java.io.FileReader;
import java.util.ArrayList;

import au.com.bytecode.opencsv.CSVReader;

import dao.DbManager;

public class CSVLoader {
	private char separator;
	private static final String ITEM_TABLE = "item_info";
	private static final String CITY_TALBE = "city";
	private static final String COMPANY_TABLE = "company";
	private static final String COUNTRY_TABLE = "country";
	private static final String DISTRICT_TABLE = "district";
	private static final String PROVINCE_TABLE = "province";
	private static final String TYPE_TABLE = "type";
	private static final String[] ITEM_INFO_LIST = { "item_id", "name",
			"company_id", "type_id", "quantity", "description", "create_time",
			"last_modify_time" };
	private static final String[] CITY_LIST = { "city_id", "name",
			"province_id", "description" };
	private static final String[] COMPANY_LIST = { "company_id", "name",
			"address", "latitude", "longtitude", "country_id", "province_id",
			"city_id", "district_id", "description", "email", "tel", "fax",
			"website" };
	private static final String[] COUNTRY_LIST = { "country_id", "name",
			"country_code", "description" };
	private static final String[] DISTRICT_LIST = { "district_id", "name",
			"city_id", "description" };
	private static final String[] PROVINCE_LIST = { "province_id", "name",
			"country_id", "description" };
	private static final String[] TYPE_LIST = { "type_id", "name",
			"description" };
	private String tableName = null;

	public CSVLoader() {
		this.separator = ',';
	}

	/**
	 * Parse CSV file using OpenCSV library and load in given database table.
	 * 
	 * @param csvFile
	 *            Input CSV file
	 * @param tableName
	 *            Database table name to import data
	 * @param truncateBeforeLoad
	 *            Truncate the table before inserting new records.
	 * @throws Exception
	 */
	public void loadCSV(String csvFile, boolean truncateBeforeLoad)
			throws Exception {
		CSVReader csvReader = null;
		String[] headerRow = null;
		String[] records = null;
		String sql = null;
		String sqlInitial = null;

		if (DbManager.getConnection() == null) {
			throw new Exception("Not a valid connection.");
		}

		try {
			csvReader = new CSVReader(new FileReader(csvFile), this.separator);
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception("Error occured while executing file. "
					+ e.getMessage());
		}

		if ((headerRow = csvReader.readNext()) != null) {
			sqlInitial = getSql(headerRow);
		} else {
			csvReader.close();
			throw new FileNotFoundException(
					"No columns defined in given CSV file."
							+ "Please check the CSV file format.");
		}

		try {
			// delete data from table before loading csv
			if (truncateBeforeLoad) {
				DbManager.execute("DELETE FROM " + tableName);
			}

			sql = sqlInitial;
			while ((records = csvReader.readNext()) != null) {
				for (String record : records) {
					sql = sql + "'" + record + "',";
				}
				sql = sql.substring(0, sql.lastIndexOf(",")) + ")";
				System.out.println(sql);
				DbManager.execute(sql);
				sql = sqlInitial;
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception(
					"Error occured while loading data from file to database."
							+ e.getMessage());
		} finally {
			csvReader.close();
		}
	}
	
	private String getSql(String[] headerRow) throws Exception{
		String sql = "insert into `";;
		if (arrayMatch(headerRow, ITEM_INFO_LIST)) {
			tableName = TYPE_TABLE;
			sql = sql + tableName + "`(";
			for (String list : ITEM_INFO_LIST) {
				sql = sql + list + ", ";
			}
		} else if (arrayMatch(headerRow, CITY_LIST)) {
			tableName = CITY_TALBE;
			sql = sql + tableName + "`(";
			for (String list : CITY_LIST) {
				sql = sql + list + ", ";
			}
		} else if (arrayMatch(headerRow, COMPANY_LIST)) {
			tableName = COMPANY_TABLE;
			sql = sql + tableName + "`(";
			for (String list : COMPANY_LIST) {
				sql = sql + list + ", ";
			}
		} else if (arrayMatch(headerRow, COUNTRY_LIST)) {
			tableName = COUNTRY_TABLE;
			sql = sql + tableName + "`(";
			for (String list : COUNTRY_LIST) {
				sql = sql + list + ", ";
			}
		} else if (arrayMatch(headerRow, DISTRICT_LIST)) {
			tableName = DISTRICT_TABLE;
			sql = sql + tableName + "`(";
			for (String list : DISTRICT_LIST) {
				sql = sql + list + ", ";
			}
		} else if (arrayMatch(headerRow, PROVINCE_LIST)) {
			tableName = PROVINCE_TABLE;
			sql = sql + tableName + "`(";
			for (String list : PROVINCE_LIST) {
				sql = sql + list + ", ";
			}
		} else if (arrayMatch(headerRow, TYPE_LIST)) {
			tableName = TYPE_TABLE;
			sql = sql + tableName + "`(";
			for (String list : TYPE_LIST) {
				sql = sql + list + ", ";
			}
		} else {
			throw new Exception(
					"columns defined in CSV file is incorrect, please check");
		}
		sql = sql.substring(0, sql.lastIndexOf(",")) + ") values (";
		return sql;
	}
	private boolean arrayMatch(String[] headerRow, String[] tableList){
		boolean match = true;
		if(headerRow.length != tableList.length){
			match = false;
		}else{
			for(int i = 0; i < headerRow.length; i ++){
				if(!headerRow[i].equals(tableList[i])){
					match = false;
					break;
				}
			}
		}
		return match;
	}

}
